/**********************************************************************************************************************
 * SplendidCRM is a Customer Relationship Management program created by SplendidCRM Software, Inc. 
 * Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved.
 * 
 * This program is free software: you can redistribute it and/or modify it under the terms of the 
 * GNU Affero General Public License as published by the Free Software Foundation, either version 3 
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
 * See the GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License along with this program. 
 * If not, see <http://www.gnu.org/licenses/>. 
 * 
 * You can contact SplendidCRM Software, Inc. at email address support@splendidcrm.com. 
 * 
 * In accordance with Section 7(b) of the GNU Affero General Public License version 3, 
 * the Appropriate Legal Notices must display the following words on all interactive user interfaces: 
 * "Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved."
 *********************************************************************************************************************/
using System;
using System.IO;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Xml;
using System.Web;
using System.Collections;
using System.Diagnostics;

namespace SplendidCRM
{
	/// <summary>
	/// Summary description for SplendidExport.
	/// </summary>
	public class SplendidExport
	{
		// 10/05/2009 Paul.  If we are using custom paging, we need to change the way we export the entire page. 
		public static void Export(DataView vw, string sModuleName, string sExportFormat, string sExportRange, int nCurrentPage, int nPageSize, string[] arrID, bool bCustomPaging)
		{
			int nStartRecord = 0;
			int nEndRecord   = vw.Count;
			switch ( sExportRange )
			{
				case "Page":
				{
					// 10/05/2009 Paul.  When using custom paging, we want to return all records when exporting the entire page. 
					if ( !bCustomPaging )
					{
						nStartRecord = nCurrentPage * nPageSize;
						nEndRecord   = Math.Min(nStartRecord + nPageSize, vw.Count);
					}
					break;
				}
				case "Selected":
				{
					// 10/17/2006 Paul.  There must be one selected record to continue. 
					if ( arrID == null )
					{
						L10N L10n = HttpContext.Current.Items["L10n"] as L10N;
						throw(new Exception(L10n.Term(".LBL_LISTVIEW_NO_SELECTED")));
					}
					StringBuilder sbIDs = new StringBuilder();
					int nCount = 0;
					foreach(string item in arrID)
					{
						if ( nCount > 0 )
							sbIDs.Append(" or ");
						// 04/30/2011 Paul.  RowFilter does not like using Guids in the IN clause, but we could use convert to solve the problem. 
						// http://weblogs.asp.net/emilstoichev/archive/2008/03/26/tip-rowfilter-with-in-operator-over-a-column-of-type-guid.aspx
						//sbIDs.Append("Convert('" + item.Replace("\'", "\'\'") + "', 'System.Guid')");
						sbIDs.AppendLine("ID = \'" + item.Replace("\'", "\'\'") + "\'");
						nCount++;
					}
					//vw.RowFilter = "ID in (" + sbIDs.ToString() + ")";
					// 11/03/2006 Paul.  A filter might already exist, so make sure to maintain the existing filter. 
					if ( vw.RowFilter.Length > 0 )
						vw.RowFilter = " and (" + sbIDs.ToString() + ")";
					else
						vw.RowFilter = sbIDs.ToString();
					nEndRecord = vw.Count;
					break;
				}
			}
			
			HttpResponse Response = HttpContext.Current.Response;
			StringBuilder sb = new StringBuilder();
			switch ( sExportFormat )
			{
				case "csv"  :
					Response.ContentType = "text/csv";
					// 08/06/2008 yxy21969.  Make sure to encode all URLs. 
					// 12/20/2009 Paul.  Use our own encoding so that a space does not get converted to a +. 
					Response.AddHeader("Content-Disposition", "attachment;filename=" + Utils.ContentDispositionEncode(HttpContext.Current.Request.Browser, sModuleName + ".csv"));
					ExportDelimited(Response.OutputStream, vw, sModuleName.ToLower(), nStartRecord, nEndRecord, ',' );
					Response.End();
					break;
				case "tab"  :
					Response.ContentType = "text/txt";
					// 08/06/2008 yxy21969.  Make sure to encode all URLs. 
					// 12/20/2009 Paul.  Use our own encoding so that a space does not get converted to a +. 
					Response.AddHeader("Content-Disposition", "attachment;filename=" + Utils.ContentDispositionEncode(HttpContext.Current.Request.Browser, sModuleName + ".txt"));
					ExportDelimited(Response.OutputStream, vw, sModuleName.ToLower(), nStartRecord, nEndRecord, '\t');
					Response.End();
					break;
				case "xml"  :
					Response.ContentType = "text/xml";
					// 08/06/2008 yxy21969.  Make sure to encode all URLs. 
					// 12/20/2009 Paul.  Use our own encoding so that a space does not get converted to a +. 
					Response.AddHeader("Content-Disposition", "attachment;filename=" + Utils.ContentDispositionEncode(HttpContext.Current.Request.Browser, sModuleName + ".xml"));
					ExportXml(Response.OutputStream, vw, sModuleName.ToLower(), nStartRecord, nEndRecord);
					Response.End();
					break;
				//case "Excel":
				default     :
					Response.ContentType = "application/vnd.ms-excel";
					// 08/06/2008 yxy21969.  Make sure to encode all URLs. 
					// 12/20/2009 Paul.  Use our own encoding so that a space does not get converted to a +. 
					Response.AddHeader("Content-Disposition", "attachment;filename=" + Utils.ContentDispositionEncode(HttpContext.Current.Request.Browser, sModuleName + ".xlb"));
					ExportExcel(Response.OutputStream, vw, sModuleName.ToLower(), nStartRecord, nEndRecord);
					Response.End();
					break;
			}
			//vw.RowFilter = null;
		}

		private static void ExportExcel(Stream stm, DataView vw, string sModuleName, int nStartRecord, int nEndRecord)
		{
			XmlTextWriter xw = new XmlTextWriter(stm, Encoding.UTF8);
			xw.Formatting  = Formatting.Indented;
			xw.IndentChar  = ControlChars.Tab;
			xw.Indentation = 1;
			xw.WriteStartDocument();
			xw.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");

			xw.WriteStartElement("Workbook");
				xw.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
				xw.WriteAttributeString("xmlns:o", "urn:schemas-microsoft-com:office:office");
				xw.WriteAttributeString("xmlns:x", "urn:schemas-microsoft-com:office:excel");
				xw.WriteAttributeString("xmlns:ss", "urn:schemas-microsoft-com:office:spreadsheet");
				xw.WriteAttributeString("xmlns:html", "http://www.w3.org/TR/REC-html40");

			xw.WriteStartElement("DocumentProperties");
				xw.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:office");
				xw.WriteStartElement("Author");
					xw.WriteString(Security.FULL_NAME);
				xw.WriteEndElement();
				xw.WriteStartElement("Created");
					xw.WriteString(DateTime.Now.ToUniversalTime().ToString("s"));
				xw.WriteEndElement();
				xw.WriteStartElement("Version");
					xw.WriteString("11.6568");
				xw.WriteEndElement();
			xw.WriteEndElement();
			xw.WriteStartElement("ExcelWorkbook");
				xw.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:excel");
				xw.WriteStartElement("WindowHeight");
					xw.WriteString("15465");
				xw.WriteEndElement();
				xw.WriteStartElement("WindowWidth");
					xw.WriteString("23820");
				xw.WriteEndElement();
				xw.WriteStartElement("WindowTopX");
					xw.WriteString("120");
				xw.WriteEndElement();
				xw.WriteStartElement("WindowTopY");
					xw.WriteString("75");
				xw.WriteEndElement();
				xw.WriteStartElement("ProtectStructure");
					xw.WriteString("False");
				xw.WriteEndElement();
				xw.WriteStartElement("ProtectWindows");
					xw.WriteString("False");
				xw.WriteEndElement();
			xw.WriteEndElement();

			xw.WriteStartElement("Styles");
				xw.WriteStartElement("Style");
					xw.WriteAttributeString("ss:ID", "Default");
					xw.WriteAttributeString("ss:Name", "Normal");
					xw.WriteStartElement("Alignment");
						xw.WriteAttributeString("ss:Vertical", "Bottom");
					xw.WriteEndElement();
					xw.WriteStartElement("Borders");
					xw.WriteEndElement();
					xw.WriteStartElement("Font");
					xw.WriteEndElement();
					xw.WriteStartElement("Interior");
					xw.WriteEndElement();
					xw.WriteStartElement("NumberFormat");
					xw.WriteEndElement();
					xw.WriteStartElement("Protection");
					xw.WriteEndElement();
				xw.WriteEndElement();
				xw.WriteStartElement("Style");
					xw.WriteAttributeString("ss:ID", "s21");
					xw.WriteStartElement("NumberFormat");
						xw.WriteAttributeString("ss:Format", "General Date");
					xw.WriteEndElement();
				xw.WriteEndElement();
			xw.WriteEndElement();

			DataTable tbl = vw.Table;
			xw.WriteStartElement("Worksheet");
				xw.WriteAttributeString("ss:Name", sModuleName);
			xw.WriteStartElement("Table");
				xw.WriteAttributeString("ss:ExpandedColumnCount", tbl.Columns.Count.ToString());
				xw.WriteAttributeString("ss:FullColumns"        , tbl.Columns.Count.ToString());
				// 11/03/2006 Paul.  Add one row for the header. 
				xw.WriteAttributeString("ss:ExpandedRowCount"   , (nEndRecord - nStartRecord + 1).ToString());

			xw.WriteStartElement("Row");
			for ( int nColumn = 0; nColumn < tbl.Columns.Count; nColumn++ )
			{
				DataColumn col = tbl.Columns[nColumn];
				xw.WriteStartElement("Cell");
				xw.WriteStartElement("Data");
				xw.WriteAttributeString("ss:Type", "String");
				xw.WriteString(col.ColumnName.ToLower());
				xw.WriteEndElement();
				xw.WriteEndElement();
			}
			xw.WriteEndElement();
			for ( int i = nStartRecord; i < nEndRecord; i++ )
			{
				xw.WriteStartElement("Row");
				DataRowView row = vw[i];
				for ( int nColumn = 0; nColumn < tbl.Columns.Count; nColumn++ )
				{
					DataColumn col = tbl.Columns[nColumn];
					xw.WriteStartElement("Cell");
					// 11/03/2006 Paul.  The style must be set in order for a date to be displayed properly. 
					if ( col.DataType.FullName == "System.DateTime" && row[nColumn] != DBNull.Value )
						xw.WriteAttributeString("ss:StyleID", "s21");
					xw.WriteStartElement("Data");
					if ( row[nColumn] != DBNull.Value )
					{
						switch ( col.DataType.FullName )
						{
							case "System.Boolean" :
								xw.WriteAttributeString("ss:Type", "String");
								xw.WriteString(Sql.ToBoolean (row[nColumn]) ? "1" : "0");
								break;
							case "System.Single"  :
								xw.WriteAttributeString("ss:Type", "Number");
								xw.WriteString(Sql.ToDouble  (row[nColumn]).ToString() );
								break;
							case "System.Double"  :
								xw.WriteAttributeString("ss:Type", "Number");
								xw.WriteString(Sql.ToDouble  (row[nColumn]).ToString() );
								break;
							case "System.Int16"   :
								xw.WriteAttributeString("ss:Type", "Number");
								xw.WriteString(Sql.ToInteger (row[nColumn]).ToString() );
								break;
							case "System.Int32"   :
								xw.WriteAttributeString("ss:Type", "Number");
								xw.WriteString(Sql.ToInteger (row[nColumn]).ToString() );
								break;
							case "System.Int64"   :
								xw.WriteAttributeString("ss:Type", "Number");
								xw.WriteString(Sql.ToLong    (row[nColumn]).ToString() );
								break;
							case "System.Decimal" :
								xw.WriteAttributeString("ss:Type", "Number");
								xw.WriteString(Sql.ToDecimal (row[nColumn]).ToString() );
								break;
							case "System.DateTime":
								xw.WriteAttributeString("ss:Type", "DateTime");
								xw.WriteString(Sql.ToDateTime(row[nColumn]).ToUniversalTime().ToString("s"));
								break;
							case "System.Guid"    :
								xw.WriteAttributeString("ss:Type", "String");
								xw.WriteString(Sql.ToGuid    (row[nColumn]).ToString().ToUpper());
								break;
							case "System.String"  :
								xw.WriteAttributeString("ss:Type", "String");
								xw.WriteString(Sql.ToString  (row[nColumn]));
								break;
							case "System.Byte[]"  :
							{
								xw.WriteAttributeString("ss:Type", "String");
								byte[] buffer = Sql.ToByteArray((System.Array) row[nColumn]);
								xw.WriteBase64(buffer, 0, buffer.Length);
								break;
							}
							default:
								//	throw(new Exception("Unsupported field type: " + rdr.GetFieldType(nColumn).FullName));
								// 11/03/2006 Paul.  We need to write the type even for empty cells. 
								xw.WriteAttributeString("ss:Type", "String");
								break;
						}
					}
					else
					{
						// 11/03/2006 Paul.  We need to write the type even for empty cells. 
						xw.WriteAttributeString("ss:Type", "String");
					}
					xw.WriteEndElement();
					xw.WriteEndElement();
				}
				xw.WriteEndElement();
			}
			xw.WriteEndElement();  // Table
			xw.WriteStartElement("WorksheetOptions");
				xw.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:excel");
				xw.WriteStartElement("Selected");
				xw.WriteEndElement();
				xw.WriteStartElement("ProtectObjects");
					xw.WriteString("False");
				xw.WriteEndElement();
				xw.WriteStartElement("ProtectScenarios");
					xw.WriteString("False");
				xw.WriteEndElement();
			xw.WriteEndElement();  // WorksheetOptions
			xw.WriteEndElement();  // Worksheet
			xw.WriteEndElement();  // Workbook
			xw.WriteEndDocument();
			xw.Flush();
		}

		private static void ExportXml(Stream stm, DataView vw, string sModuleName, int nStartRecord, int nEndRecord)
		{
			XmlTextWriter xw = new XmlTextWriter(stm, Encoding.UTF8);
			xw.Formatting  = Formatting.Indented;
			xw.IndentChar  = ControlChars.Tab;
			xw.Indentation = 1;
			xw.WriteStartDocument();
			xw.WriteStartElement("splendidcrm");

			DataTable tbl = vw.Table;
			for ( int i = nStartRecord; i < nEndRecord; i++ )
			{
				xw.WriteStartElement(sModuleName);
				DataRowView row = vw[i];
				for ( int nColumn = 0; nColumn < tbl.Columns.Count; nColumn++ )
				{
					DataColumn col = tbl.Columns[nColumn];
					xw.WriteStartElement(col.ColumnName.ToLower());
					if ( row[nColumn] != DBNull.Value )
					{
						switch ( col.DataType.FullName )
						{
							case "System.Boolean" :  xw.WriteString(Sql.ToBoolean (row[nColumn]) ? "1" : "0");  break;
							case "System.Single"  :  xw.WriteString(Sql.ToDouble  (row[nColumn]).ToString() );  break;
							case "System.Double"  :  xw.WriteString(Sql.ToDouble  (row[nColumn]).ToString() );  break;
							case "System.Int16"   :  xw.WriteString(Sql.ToInteger (row[nColumn]).ToString() );  break;
							case "System.Int32"   :  xw.WriteString(Sql.ToInteger (row[nColumn]).ToString() );  break;
							case "System.Int64"   :  xw.WriteString(Sql.ToLong    (row[nColumn]).ToString() );  break;
							case "System.Decimal" :  xw.WriteString(Sql.ToDecimal (row[nColumn]).ToString() );  break;
							case "System.DateTime":  xw.WriteString(Sql.ToDateTime(row[nColumn]).ToUniversalTime().ToString(CalendarControl.SqlDateTimeFormat));  break;
							case "System.Guid"    :  xw.WriteString(Sql.ToGuid    (row[nColumn]).ToString().ToUpper());  break;
							case "System.String"  :  xw.WriteString(Sql.ToString  (row[nColumn]));  break;
							case "System.Byte[]"  :
							{
								byte[] buffer = Sql.ToByteArray((System.Array) row[nColumn]);
								xw.WriteBase64(buffer, 0, buffer.Length);
								break;
							}
							//default:
							//	throw(new Exception("Unsupported field type: " + rdr.GetFieldType(nColumn).FullName));
						}
					}
					xw.WriteEndElement();
				}
				xw.WriteEndElement();
			}
			xw.WriteEndElement();
			xw.WriteEndDocument();
			xw.Flush();
		}

		private static void ExportDelimited(Stream stm, DataView vw, string sModuleName, int nStartRecord, int nEndRecord, char chDelimiter)
		{
			StreamWriter wt = new StreamWriter(stm);
			DataTable tbl = vw.Table;
			for ( int nColumn = 0; nColumn < tbl.Columns.Count; nColumn++ )
			{
				if ( nColumn > 0 )
					wt.Write(chDelimiter);
				DataColumn col = tbl.Columns[nColumn];
				wt.Write(col.ColumnName.ToLower());
			}
			wt.WriteLine("");

			for ( int i = nStartRecord; i < nEndRecord; i++ )
			{
				DataRowView row = vw[i];
				for ( int nColumn = 0; nColumn < tbl.Columns.Count; nColumn++ )
				{
					if ( nColumn > 0 )
						wt.Write(chDelimiter);
					DataColumn col = tbl.Columns[nColumn];
					if ( row[nColumn] != DBNull.Value )
					{
						string sValue = String.Empty;
						switch ( col.DataType.FullName )
						{
							case "System.Boolean" :  sValue = Sql.ToBoolean (row[nColumn]) ? "1" : "0";  break;
							case "System.Single"  :  sValue = Sql.ToDouble  (row[nColumn]).ToString() ;  break;
							case "System.Double"  :  sValue = Sql.ToDouble  (row[nColumn]).ToString() ;  break;
							case "System.Int16"   :  sValue = Sql.ToInteger (row[nColumn]).ToString() ;  break;
							case "System.Int32"   :  sValue = Sql.ToInteger (row[nColumn]).ToString() ;  break;
							case "System.Int64"   :  sValue = Sql.ToLong    (row[nColumn]).ToString() ;  break;
							case "System.Decimal" :  sValue = Sql.ToDecimal (row[nColumn]).ToString() ;  break;
							case "System.DateTime":  sValue = Sql.ToDateTime(row[nColumn]).ToUniversalTime().ToString(CalendarControl.SqlDateTimeFormat);  break;
							case "System.Guid"    :  sValue = Sql.ToGuid    (row[nColumn]).ToString().ToUpper();  break;
							case "System.String"  :  sValue = Sql.ToString  (row[nColumn]);  break;
							case "System.Byte[]"  :
							{
								byte[] buffer = Sql.ToByteArray((System.Array) row[0]);
								sValue = Convert.ToBase64String(buffer, 0, buffer.Length);
								break;
							}
							//default:
							//	throw(new Exception("Unsupported field type: " + rdr.GetFieldType(nColumn).FullName));
						}
						if( sValue.IndexOf(chDelimiter) >= 0 || sValue.IndexOf('\"') >= 0 )
							sValue = "\"" + sValue.Replace("\"", "\"\"") + "\"";
						wt.Write(sValue);
					}
				}
				wt.WriteLine("");
			}
			wt.Flush();
		}
	}
}


